package com.lagou.studentsystem.dao.Impl;

import com.lagou.studentsystem.dao.StudentDao;
import com.lagou.studentsystem.model.PageBean;
import com.lagou.studentsystem.model.Student;
import com.lagou.studentsystem.util.DBUtil;
import com.lagou.studentsystem.util.DruidUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;



    /**
     * 自定义成员方法描述显示所有学生的功能
     * @param pageBean
     * @return
     */
    @Override
    public List<Student> studentShow(PageBean pageBean) {
        List<Student> studentList = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.获取Connection连接对象
            connection = DruidUtils.getConnection();
            // 2.使用StringBuilder类型的对象来描述SQL语句的目的在于后续可以发生改变
            StringBuilder stringBuilder = new StringBuilder("select * from student");
            // 3.当输入分页的需求时进行分页查询，于是拼接SQL语句
            if (pageBean != null) {
                stringBuilder.append(" limit " + pageBean.getStart() + "," + pageBean.getRow());
            }
            // 4.获取PreparedStatement并执行上述SQL语句
            preparedStatement = connection.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            // 5.将结果集中的数据取出并放到List集合中返回
            studentList = new ArrayList<>();
            while (resultSet.next()) {
                Student student = new Student(
                        resultSet.getString("stu_id"),
                        resultSet.getString("stu_name"),
                        resultSet.getString("stu_sex"),
                        resultSet.getString("stu_birthday"),
                        resultSet.getString("stu_email"),
                        resultSet.getString("stu_remarks"),
                        resultSet.getString("stu_class")
                        );
                studentList.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtils.close(connection, preparedStatement, resultSet);
        }
        return studentList;
    }


    @Override
    public List<Student> getStudentAll() {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "select * from student";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            List<Student> studentList = new ArrayList<>();
            while (resultSet.next()){
                Student stu = new Student();
                stu.setStu_id(resultSet.getString("stu_id"));
                stu.setStu_name(resultSet.getString("stu_name"));
                stu.setStu_birthday(resultSet.getString("stu_birthday"));
                stu.setStu_email(resultSet.getString("stu_email"));
                stu.setStu_sex(resultSet.getString("stu_sex"));
                stu.setStu_remarks(resultSet.getString("stu_remarks"));
                stu.setStu_class(resultSet.getString("stu_class"));
                studentList.add(stu);
            }
            return studentList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement,resultSet);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public int insertStudent(Student student) {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "insert into student values (?,?,?,?,?,?,?) ;";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getStu_id());
            preparedStatement.setString(2,student.getStu_name());
            preparedStatement.setString(3,student.getStu_sex());
            preparedStatement.setString(4,student.getStu_birthday());
            preparedStatement.setString(5,student.getStu_email());
            preparedStatement.setString(6,student.getStu_remarks());
            preparedStatement.setString(7,student.getStu_class());
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public int deleteStudentById(String id) {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "delete from student where stu_id = ? ;";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,id);
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public int updateStudentById(String id,Student student) {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "update student set stu_id = ? ,stu_name = ?,stu_sex = ?,stu_birthday = ?,stu_email = ?,stu_remarks = ? ,stu_class = ?where stu_id = ? ;";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getStu_id());
            preparedStatement.setString(2,student.getStu_name());
            preparedStatement.setString(3,student.getStu_sex());
            preparedStatement.setString(4,student.getStu_birthday());
            preparedStatement.setString(5,student.getStu_email());
            preparedStatement.setString(6,student.getStu_remarks());
            preparedStatement.setString(7,student.getStu_class());
            preparedStatement.setString(8,id);
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public Student getStudentById(String id) {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "select * from student where stu_id = ? ";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,id);
            resultSet = preparedStatement.executeQuery();
            Student stu = new Student();
            while (resultSet.next()){
                stu.setStu_id(resultSet.getString("stu_id"));
                stu.setStu_name(resultSet.getString("stu_name"));
                stu.setStu_birthday(resultSet.getString("stu_birthday"));
                stu.setStu_email(resultSet.getString("stu_email"));
                stu.setStu_sex(resultSet.getString("stu_sex"));
                stu.setStu_remarks(resultSet.getString("stu_remarks"));
                stu.setStu_class(resultSet.getString("stu_class"));
            }
            return stu;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement,resultSet);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public List<Student> getStudentByName(String name) {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "select * from student where stu_name = ? ";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            resultSet = preparedStatement.executeQuery();
            List<Student> studentList = new ArrayList<>();
            Student stu = new Student();
            while (resultSet.next()){
                stu.setStu_id(resultSet.getString("stu_id"));
                stu.setStu_name(resultSet.getString("stu_name"));
                stu.setStu_birthday(resultSet.getString("stu_birthday"));
                stu.setStu_email(resultSet.getString("stu_email"));
                stu.setStu_sex(resultSet.getString("stu_sex"));
                stu.setStu_remarks(resultSet.getString("stu_remarks"));
                stu.setStu_class(resultSet.getString("stu_class"));
                studentList.add(stu);
            }
            return studentList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement,resultSet);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public List<Student> getStudentByClassId(String c_id){
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "select * from student where stu_class = ? ";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,c_id);
            resultSet = preparedStatement.executeQuery();
            List<Student> studentList = new ArrayList<>();
            Student stu = new Student();
            while (resultSet.next()){
                stu.setStu_id(resultSet.getString("stu_id"));
                stu.setStu_name(resultSet.getString("stu_name"));
                stu.setStu_birthday(resultSet.getString("stu_birthday"));
                stu.setStu_email(resultSet.getString("stu_email"));
                stu.setStu_sex(resultSet.getString("stu_sex"));
                stu.setStu_remarks(resultSet.getString("stu_remarks"));
                stu.setStu_class(resultSet.getString("stu_class"));
                studentList.add(stu);
            }
            return studentList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement,resultSet);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}
